package cn.iocoder.yudao.module.def.service.project;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.common.util.http.HttpUtils;
import cn.iocoder.yudao.framework.common.util.object.BeanUtils;
import cn.iocoder.yudao.framework.excel.core.util.ExcelUtils;
import cn.iocoder.yudao.framework.security.core.LoginUser;
import cn.iocoder.yudao.framework.security.core.util.SecurityFrameworkUtils;
import cn.iocoder.yudao.module.def.controller.admin.project.vo.*;
import cn.iocoder.yudao.module.def.controller.admin.project.vo.excel.*;
import cn.iocoder.yudao.module.def.dal.dataobject.code.CodeDO;
import cn.iocoder.yudao.module.def.dal.dataobject.common.ProjectYearCountDo;
import cn.iocoder.yudao.module.def.dal.dataobject.project.ProjectDO;
import cn.iocoder.yudao.module.def.dal.mysql.project.ProjectMapper;
import cn.iocoder.yudao.module.def.service.code.CodeService;
import cn.iocoder.yudao.module.def.service.projectprogress.ProjectProgressService;
import cn.iocoder.yudao.module.system.dal.dataobject.dept.DeptDO;
import cn.iocoder.yudao.module.system.dal.dataobject.user.AdminUserDO;
import cn.iocoder.yudao.module.system.dal.mysql.user.AdminUserMapper;
import cn.iocoder.yudao.module.system.service.dept.DeptService;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;
import java.util.stream.Collectors;

import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.yudao.module.def.enums.ErrorCodeConstants.IMPORT_LIST_IS_EMPTY;
import static cn.iocoder.yudao.module.def.enums.ErrorCodeConstants.PROJECT_NOT_EXISTS;

/**
 * 工程 Service 实现类
 *
 * @author 芋道源码
 */
@Service
@Validated
@Slf4j
public class ProjectServiceImpl implements ProjectService {

    @Resource
    private ProjectMapper projectMapper;

    @Resource
    private CodeService codeService;
    @Resource
    private DeptService deptService;

    @Resource
    private ProjectProgressService projectProgressService;
    @Resource
    private AdminUserMapper userMapper;


    @Override
    public Integer createProject(ProjectSaveReqVO createReqVO) {
        // 插入
        ProjectDO project = BeanUtils.toBean(createReqVO, ProjectDO.class);
        List<String> imgs = createReqVO.getImgs();
        if (CollUtil.isNotEmpty(imgs)) {
            project.setImgs(JSONObject.toJSONString(imgs));
        }
        List<String> videos = createReqVO.getVideos();
        if (CollUtil.isNotEmpty(videos)) {
            project.setVideos(JSONObject.toJSONString(videos));
        }
        projectMapper.insert(project);
//        ProjectProgressSaveReqVO projectProgressSaveReqVO = createReqVO.getProjectProgressSaveReqVO();
//        projectProgressSaveReqVO.setProjectId(project.getId());
//        projectProgressService.createProjectProgress(projectProgressSaveReqVO);
        // 返回
        return project.getId();
    }

    @Override
    public void updateProject(ProjectSaveReqVO updateReqVO) {
        // 校验存在
        validateProjectExists(updateReqVO.getId());

        // 更新
        ProjectDO updateObj = BeanUtils.toBean(updateReqVO, ProjectDO.class);
        List<String> imgs = updateReqVO.getImgs();
        if (CollUtil.isNotEmpty(imgs)) {
            updateObj.setImgs(JSONObject.toJSONString(imgs));
        }
        List<String> videos = updateReqVO.getVideos();
        if (CollUtil.isNotEmpty(videos)) {
            updateObj.setVideos(JSONObject.toJSONString(videos));
        }
//        ProjectProgressSaveReqVO projectProgressSaveReqVO = updateReqVO.getProjectProgressSaveReqVO();
//        projectProgressSaveReqVO.setProjectId(updateReqVO.getId());
//        projectProgressService.updateProjectProgress(projectProgressSaveReqVO);
        projectMapper.updateById(updateObj);
    }

    public void updateProject(List<ProjectRespVO> updateReqVOs) {
        // 更新
        List<ProjectDO> list = BeanUtils.toBean(updateReqVOs, ProjectDO.class);
        projectMapper.insertBatch(list);
    }

    @Override
    public void deleteProject(Integer id) {
        // 校验存在
        validateProjectExists(id);
        // 删除
        projectMapper.deleteById(id);
    }

    private void validateProjectExists(Integer id) {
        if (projectMapper.selectById(id) == null) {
            throw exception(PROJECT_NOT_EXISTS);
        }
    }

    @Override
    public ProjectDO getProject(Integer id) {
        return projectMapper.selectById(id);
    }

    @Override
    public PageResult<ProjectDO> getProjectPage(ProjectPageReqVO pageReqVO) {
        String projectCate = pageReqVO.getProjectCate();
        if (ObjectUtils.isNotEmpty(projectCate)) {
            pageReqVO.setProjectCates(projectCate.split(","));
        }
        return projectMapper.selectPage(pageReqVO);
    }

    @Override
    public List<ProjectDO> selectAll(ProjectPageImportReqVO pageReqVO) {
        String projectCate = pageReqVO.getProjectCate();
        if (ObjectUtils.isNotEmpty(projectCate)) {
            pageReqVO.setProjectCates(projectCate.split(","));
        }
        return projectMapper.selectList();
    }

    @Override
    public Map<String, Object> selectInvestmentsGroupedByCity(String city) {
        // 获取分组后的投资数据
        List<Map<String, Object>> investments = projectMapper.selectInvestmentsGroupedByCity(city);

        // 初始化结果列表
        List<Double> centralInvestments = new ArrayList<>();
        List<Double> localInvestments = new ArrayList<>();
        List<String> areas = new ArrayList<>();

        // 遍历查询结果，填充数据
        for (Map<String, Object> investment : investments) {
            try {
                if (MapUtil.isNotEmpty(investment)) {
                    Object nameObj = investment.get("name");
                    if (nameObj == null || nameObj.equals("")) {
                        continue;
                    }
                    Object investCentralObj = investment.get("invest_central");
                    Object investLocalObj = investment.get("invest_local");

                    if (nameObj instanceof String && investCentralObj instanceof Number && investLocalObj instanceof Number) {
                        String name = (String) nameObj;
                        double investCentral = ((Number) investCentralObj).doubleValue() / 10000;
                        double investLocal = ((Number) investLocalObj).doubleValue() / 10000;

                        areas.add(name);
                        centralInvestments.add(investCentral);
                        localInvestments.add(investLocal);
                    } else {
                        log.warn("数据类型不匹配: name={}, invest_central={}, invest_local={}", nameObj, investCentralObj, investLocalObj);
                    }
                }

            } catch (Exception e) {
                log.error("数据转换异常", e);
            }
        }

        // 构建返回的JSON结构
        Map<String, Object> result = new HashMap<>();
        result.put("legend", new String[]{"中央投资金额", "地方投资金额"});
        result.put("area", areas);

        // 使用ArrayList代替List.of
        List<List<Double>> data = new ArrayList<>();
        data.add(centralInvestments);
        data.add(localInvestments);

        result.put("data", data);

        return result;
    }

    @Override
    public List<Map<String, Object>> selectProjectCountsByCityAndYear(String city, String xmType) {
        // 获取按城市和年份分组的统计数据
        List<ProjectYearCountDo> rawResults;
        if (Objects.equals("zqdl", xmType)) {
            rawResults = projectMapper.selectZqdlProjectCountsByCityAndYear(city, xmType);
        } else {
            rawResults = projectMapper.selectProjectCountsByCityAndYear(city, xmType);
        }

        // 使用Map按城市分组
        Map<String, List<ProjectYearCountDo>> groupedByCity = rawResults.stream()
                .filter(result -> result.getName() != null) // 过滤掉name为空的结果
                .collect(Collectors.groupingBy(
                        ProjectYearCountDo::getName
                ));

        // 构建最终结果
        List<Map<String, Object>> formattedData = new ArrayList<>();

        for (Map.Entry<String, List<ProjectYearCountDo>> entry : groupedByCity.entrySet()) {
            String cityName = entry.getKey();
            List<ProjectYearCountDo> yearlyData = entry.getValue();

            // 构建该城市的年份列表
            List<Map<String, Object>> list = new ArrayList<>();
            for (ProjectYearCountDo data : yearlyData) {
                Map<String, Object> item = new HashMap<>();
                if (ObjectUtils.isEmpty(data.getYear())) {
                    continue;
                }
                item.put("name", data.getYear()); // 年份
                item.put("value", data.getCount()); // 数量
                list.add(item);
            }

            // 按年份排序
//            list.sort(Comparator.comparing(o ->  o.get("name")));

            // 构建城市对象
            Map<String, Object> cityData = new HashMap<>();
            cityData.put("name", cityName);
            cityData.put("list", list);

            formattedData.add(cityData);
        }

        return formattedData;
    }

    @Override
    public List<Map<String, Object>> getOverallSituationNum(String city) {
        return projectMapper.getOverallSituationNum(city);
    }

    @Override
    public List<Map<String, Object>> getProjectTypeNum(String city, String xmType) {
        return projectMapper.getProjectTypeNum(city, xmType);
    }


    @Override
    public List<Map<String, Object>> selectProjectsWithCategory(String starTime, String endTime, String city) {
        // 获取所有项目数据及其分类
        List<Map<String, Object>> rawResults = projectMapper.selectProjectsWithCategory(starTime, endTime, city);

        // 定义颜色映射和类型映射
        Map<String, String> typeMapping = new HashMap<>();
        typeMapping.put("军警民联防平台", "军警民联防平台、监控中心");
        typeMapping.put("监控中心", "军警民联防平台、监控中心");
        typeMapping.put("监控站", "监控站、监控前端");
        typeMapping.put("监控前端", "监控站、监控前端");
        typeMapping.put("执勤道路", "执勤道路");
        typeMapping.put("执勤码头", "执勤码头、停机坪、瞭望塔");
        typeMapping.put("停机坪", "执勤码头、停机坪、瞭望塔");
        typeMapping.put("瞭望塔", "执勤码头、停机坪、瞭望塔");

        Map<String, String> colorMapping = new HashMap<>();
        colorMapping.put("军警民联防平台、监控中心", "#EF4864");
        colorMapping.put("监控站、监控前端", "#FF922D");
        colorMapping.put("执勤道路", "#FACC14");
        colorMapping.put("执勤码头、停机坪、瞭望塔", "#2FC25B");

        // 构建最终结果
        List<Map<String, Object>> formattedData = new ArrayList<>();

        for (Map<String, Object> result : rawResults) {
            String category = (String) result.get("project_category"); // 分类名称
            Object lngLat = result.get("lng_lat"); // 经纬度数组

            if (category == null || lngLat == null) continue; // 如果分类或经纬度为空，则跳过

            // 获取大类的type
            String majorType = typeMapping.getOrDefault(category, "未知类别");
            String color = colorMapping.getOrDefault(majorType, "#EF4864"); // 默认色
            // 设置 type 和 color
            Map<String, Object> projectData = new HashMap<>();
            projectData.put("type", majorType);
            projectData.put("cityName", result.get("city"));
            projectData.put("area", result.get("area"));
            projectData.put("color", color);
            projectData.put("value", new Object[]{lngLat, 6.0}); // 经纬度数组 + 高度

            formattedData.add(projectData);
        }

        return formattedData;
    }

    @Override
    public List<Map<String, Object>> getSummaryDataByCity(String cityName, String starTime, String endTime) {
        // 获取统计数据（按城市分组）
        List<Map<String, Object>> rawData = projectMapper.getSummaryDataByCity(cityName, starTime, endTime);
        if (rawData.isEmpty()) {
            rawData = projectMapper.getSummaryDataByArea(cityName, starTime, endTime);
        }

        // 构建最终结果
        List<Map<String, Object>> formattedData = new ArrayList<>();

        for (Map<String, Object> cityData : rawData) {
            Map<String, Object> formattedCityData = new HashMap<>();
            formattedCityData.put("name", cityData.get("cityName")); // 城市名称
            formattedCityData.put("xmzs", cityData.get("xmzs")); // 项目总数
            formattedCityData.put("zqdl", cityData.get("zqdl")); // 执勤道路
            formattedCityData.put("tjp", cityData.get("tjp")); // 停机坪
            formattedCityData.put("mt", cityData.get("mt")); // 码头
            formattedCityData.put("lwt", cityData.get("lwt")); // 瞭望塔
            formattedCityData.put("jjmlfpt", cityData.get("jjmlfpt")); // 军警民联防平台
            formattedCityData.put("jkzx", cityData.get("jkzx")); // 监控中心

            formattedCityData.put("jkqd", cityData.get("jkqd")); // 军警民联防平台
            formattedCityData.put("jkz", cityData.get("jkz")); //

            formattedData.add(formattedCityData);
        }

        return formattedData;
    }


    @Override
    public List<Map<String, Object>> selectProjectBuildingCountsByCity(String yearTime, String city) {
        // 获取每个城市的项目统计结果
        List<Map<String, Object>> rawResults = projectMapper.selectProjectBuildingCountsByCity(yearTime, city);

        // 构建最终结果
        List<Map<String, Object>> formattedData = new ArrayList<>();

        for (Map<String, Object> result : rawResults) {
            Map<String, Object> cityData = new HashMap<>();

            String cityName = (String) result.get("name");
            int totalProjects = ((Number) result.get("total_projects")).intValue();
            int ongoingProjects = ((Number) result.get("ongoing_projects")).intValue();

            cityData.put("name", cityName);
            cityData.put("value", String.valueOf(totalProjects)); // 项目总数
            cityData.put("zjxm", String.valueOf(ongoingProjects)); // 在建项目数量
            cityData.put("maxValue", "1000"); // 最大值固定为1000

            formattedData.add(cityData);
        }

        return formattedData;
    }

    @Override
    public List<Map<String, Object>> getFormattedProjectCompletionRateByCity(String yearTime, String city) {
        // 获取统计数据
        List<Map<String, Object>> rawData = projectMapper.selectProjectCompletionRateByCity(yearTime, city);

        // 构建最终结果
        List<Map<String, Object>> formattedData = new ArrayList<>();

        for (Map<String, Object> data : rawData) {
            Map<String, Object> formattedEntry = new HashMap<>();
            Object name = data.get("name");
            formattedEntry.put("name", name); // 城市名称
            formattedEntry.put("value", data.get("total_projects")); // 项目总数
            formattedEntry.put("zjxm", data.get("finished_projects")); // 完工项目数量
            formattedData.add(formattedEntry);
        }

        return formattedData;
    }

    @Override
    public List<Map<String, Object>> selectProjectFundCountsByCity(String yearTime, String city) {
        return projectMapper.selectPaymentRateByCityAndYear(city, yearTime);
    }

    @Override
    public ImportRespVO importUserList(Map<String, List<ProjectRespVO>> list) {
        // 1.1 参数校验
        if (CollUtil.isEmpty(list)) {
            throw exception(IMPORT_LIST_IS_EMPTY);
        }
        // 2. 遍历，逐个创建 or 更新
        ImportRespVO respVO = ImportRespVO.builder().createUsernames(new ArrayList<>())
                .updateUsernames(new ArrayList<>()).failureUsernames(new LinkedHashMap<>()).build();
        list.forEach((k, v) -> {
            for (ProjectRespVO data : v) {
                if (StringUtils.isNotBlank(k)) {
                    saveOrUpdateData(k, data);
                }
            }

        });
        return respVO;
    }

    private void saveOrUpdateData(String k, ProjectRespVO data) {
        CodeDO code = codeService.getCodeByName(k);
        String reformApprovalName = data.getActualFacilityName();
        if (ObjectUtil.isNotEmpty(code)) {
            data.setProjectCate(code.getCodeId());
        }
        if (StringUtils.isEmpty(data.getRemark())) {
            data.setRemark("暂无");
        }
        ProjectDO project = getProjectByReformApprovalName(reformApprovalName);
        ProjectDO projectDO = BeanUtils.toBean(data, ProjectDO.class);
        projectDO.setImgs(data.getImg());
        projectDO.setVideos(data.getVideo());
        if (project != null) {
            // 更新
            projectDO.setId(project.getId());
            projectMapper.updateById(projectDO);
        } else {
            // 创建
            projectMapper.insert(projectDO);
        }
    }

    public ProjectDO getProjectByReformApprovalName(String reformApprovalName) {
        LambdaQueryWrapper<ProjectDO> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(ProjectDO::getActualFacilityName, reformApprovalName);
        List<ProjectDO> projectDOList = projectMapper.selectList(queryWrapper);
        if (CollectionUtil.isEmpty(projectDOList)) {
            return null;
        }
        return projectDOList.get(0);
    }

    @Override
    public List<Map<String, String>> getCity() {

        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        assert loginUser != null;
        AdminUserDO adminUserDO = userMapper.selectById(loginUser.getId());
        List<Map<String, String>> list = new ArrayList<>();
        DeptDO dept = deptService.getDept(adminUserDO.getDeptId());
        QueryWrapper<ProjectDO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("distinct city");
        queryWrapper.ne("city", "杭州市");
        List<ProjectDO> projectDOList = projectMapper.selectList(queryWrapper);
        if (ObjectUtil.isNotEmpty(dept)) {
            if (Objects.equals(dept.getName(), "浙江省")) {
                // 确保 projectDOList 不为 null，并且过滤掉其中的 null 元素
                if (CollectionUtil.isNotEmpty(projectDOList)) {
                    projectDOList.stream()
                            .filter(Objects::nonNull) // 过滤掉 null 元素
                            .map(ProjectDO::getCity)
                            .filter(StringUtils::isNotBlank) // 过滤掉空字符串或空白字符
                            .forEach(city -> {
                                Map<String, String> map = new HashMap<>();
                                map.put("label", city);
                                map.put("value", city);
                                list.add(map);
                            });
                }

                // 添加浙江省的默认选项
                Map<String, String> zhejiangMap = new HashMap<>();
                zhejiangMap.put("label", "浙江省");
                zhejiangMap.put("value", "浙江省");
                list.add(zhejiangMap);
            } else {
                Map<String, String> map = new HashMap<>();
                map.put("label", dept.getName());
                map.put("value", dept.getName());
                list.add(map);
            }
            Collections.reverse(list);
            return list;
        }
        return Collections.emptyList();
    }

    @Override
    public List<Map<String, String>> getCityForSearch() {
        LoginUser loginUser = SecurityFrameworkUtils.getLoginUser();
        assert loginUser != null;
        AdminUserDO adminUserDO = userMapper.selectById(loginUser.getId());
        List<Map<String, String>> list = new ArrayList<>();
        DeptDO dept = deptService.getDept(adminUserDO.getDeptId());
        QueryWrapper<ProjectDO> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("distinct city");
        queryWrapper.ne("city", "杭州市");
        List<ProjectDO> projectDOList = projectMapper.selectList(queryWrapper);
        if (ObjectUtil.isNotEmpty(dept)) {
            if (Objects.equals(dept.getName(), "浙江省")) {
                // 确保 projectDOList 不为 null，并且过滤掉其中的 null 元素
                if (CollectionUtil.isNotEmpty(projectDOList)) {
                    projectDOList.stream()
                            .filter(Objects::nonNull) // 过滤掉 null 元素
                            .map(ProjectDO::getCity)
                            .filter(StringUtils::isNotBlank) // 过滤掉空字符串或空白字符
                            .forEach(city -> {
                                Map<String, String> map = new HashMap<>();
                                map.put("label", city);
                                map.put("value", city);
                                list.add(map);
                            });
                }

                // 添加浙江省的默认选项
                Map<String, String> zhejiangMap = new HashMap<>();
                zhejiangMap.put("label", "浙江省");
                zhejiangMap.put("value", "浙江省");
                list.add(zhejiangMap);
            } else {

                QueryWrapper<ProjectDO> wrapper = new QueryWrapper<>();
                wrapper.select("distinct area");
                wrapper.eq("city", dept.getName());
                List<ProjectDO> areaProjectDOList = projectMapper.selectList(wrapper);
                areaProjectDOList.stream().filter(Objects::nonNull) // 过滤掉 null 元素
                        .map(ProjectDO::getArea)
                        .filter(StringUtils::isNotBlank) // 过滤掉空字符串或空白字符
                        .forEach(city -> {
                            Map<String, String> map = new HashMap<>();
                            map.put("label", city);
                            map.put("value", city);
                            list.add(map);
                        });
            }
            Collections.reverse(list);
            return list;
        }
        return Collections.emptyList();
    }

    @Override
    public Map<String, List<ProjectRespVO>> importExcel(MultipartFile file) {
        Map<String, List<ProjectRespVO>> allData = new ConcurrentHashMap<>();

        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(inputStream, ProjectRespVO.class, new AnalysisEventListener<ProjectRespVO>() {
                        private String currentSheetName; // 当前 Sheet 名称
                        private List<ProjectRespVO> currentSheetData = new ArrayList<>(); // 当前 Sheet 的数据

                        @Override
                        public void invoke(ProjectRespVO data, AnalysisContext context) {
                            // 每解析一行数据时触发
                            currentSheetData.add(data);
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext context) {
                            // 当前 Sheet 解析完成时触发
                            allData.put(currentSheetName, currentSheetData);
                            currentSheetData = new ArrayList<>(); // 清空当前 Sheet 数据，准备处理下一个 Sheet
                        }

                        @Override
                        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                            // 获取当前 Sheet 的名称
                            currentSheetName = context.readSheetHolder().getSheetName();
                        }
                    })
                    .autoCloseStream(false)
                    .doReadAllSync(); // 自动读取所有 Sheet

        } catch (Exception e) {
            throw new RuntimeException("解析 Excel 文件失败,sheetName:{}", e);
        }

        return allData;
    }

    @Override
    public Map<String, Pair<List<?>, Class<?>>> importExcel(MultipartFile file,
                                                            Function<String, Class<?>> sheetTypeMapper) {
        Map<String, Pair<List<?>, Class<?>>> result = new LinkedHashMap<>();

        try (InputStream inputStream = file.getInputStream()) {
            // 1. 创建 ExcelReader 实例
            ExcelReader excelReader = EasyExcel.read(inputStream).build();

            // 2. 获取所有 Sheet 列表
            List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();

            // 3. 遍历处理每个 Sheet
            for (ReadSheet sheet : sheets) {
                String sheetName = sheet.getSheetName();
                Class<?> targetClass = sheetTypeMapper.apply(sheetName);

                if (targetClass == null) {
                    log.warn("Sheet [{}] 未配置数据类型映射，跳过解析", sheetName);
                    continue;
                }

                // 4. 为每个 Sheet 创建独立监听器
                List<Object> sheetData = processSingleSheet(excelReader, sheet, targetClass);

                // 5. 存储结果
                result.put(sheetName, Pair.of(sheetData, targetClass));
            }
        } catch (Exception e) {
            throw new RuntimeException("Excel 解析失败: " + e.getMessage(), e);
        }

        return result;
    }

    //===================== 处理单个 Sheet ======================//
    public static List<Object> processSingleSheet(ExcelReader excelReader,
                                                   ReadSheet sheetInfo,
                                                   Class<?> targetClass) {
        List<Object> sheetData = new ArrayList<>();

        // 1. 创建类型专用监听器
        AnalysisEventListener<Object> listener = new AnalysisEventListener<Object>() {
            @Override
            public void invoke(Object data, AnalysisContext context) {
                sheetData.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 可在此添加后处理逻辑
            }
        };

        // 2. 构建带类型映射的 Sheet 读取配置
        ReadSheet readSheet = EasyExcel.readSheet(sheetInfo.getSheetNo())
                .head(targetClass)
                .registerReadListener(listener)
                .build();

        // 3. 执行读取
        excelReader.read(readSheet);

        return sheetData;
    }

    @Override
    public List<ProjectRespVO> getProjectList(ProjectPageReqVO reqVO) {
        return projectMapper.getProjectList(reqVO);
    }

    @Override
    public void deleteBatch(List<Integer> ids) {
        projectMapper.deleteByIds(ids);
    }

    @Override
    public void exportProjectExcel(ProjectPageReqVO pageReqVO, HttpServletResponse response) {
        // 1. 获取项目列表
        List<ProjectRespVO> projectList = this.getProjectList(pageReqVO);

        if (projectList == null || projectList.isEmpty()) {
            return; // 快速返回空数据
        }

        // 2. 按codeTxt分组
        Map<String, List<ProjectRespVO>> sheetDataMap = projectList.stream()
                .filter(project -> StringUtils.isNotBlank(project.getCodeTxt()))
                .collect(Collectors.groupingBy(ProjectRespVO::getCodeTxt));

        // 3. 处理特殊分组
        Map<String, Pair<List<?>, Class<?>>> map = processSpecialGroups(sheetDataMap);

        // 4. 导出多Sheet的Excel
        writeMultipleSheets(response, "工程.xlsx", map);
    }

    @Override
    public void dealImportFile(MultipartFile file) {
        // 1. 定义 Sheet 类型映射规则
        Function<String, Class<?>> typeMapper = sheetName -> {
            switch (sheetName) {
                case "执勤道路":
                    return ZqdlVo.class;
                case "执勤码头":
                    return ZqmtVo.class;
                case "监控中心":
                    return JkzxVo.class;
                case "监控站":
                    return JkzVo.class;
                case "监控前端":
                    return JkqdVo.class;
                case "瞭望塔":
                    return LwtVo.class;
                case "直升机停机坪":
                    return ZsjtjpVo.class;
                case "军警民联防平台":
                    return JjmlfptVo.class;
                default:
                    return null;
            }
        };

        // 2. 执行导入
        Map<String, Pair<List<?>, Class<?>>> importedData =
                this.importExcel(file, typeMapper);

        // 3. 使用数据（类型安全获取）
        importedData.get("执勤道路").getLeft().stream()
                .map(obj -> (ZqdlVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("执勤道路", projectRespVO);
                });
        importedData.get("执勤码头").getLeft().stream()
                .map(obj -> (ZqmtVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("执勤码头", projectRespVO);
                });
        importedData.get("监控中心").getLeft().stream()
                .map(obj -> (JkzxVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("监控中心", projectRespVO);
                });
        importedData.get("监控站").getLeft().stream()
                .map(obj -> (JkzVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("监控站", projectRespVO);
                });
        importedData.get("监控前端").getLeft().stream()
                .map(obj -> (JkqdVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("监控前端", projectRespVO);
                });
        importedData.get("瞭望塔").getLeft().stream()
                .map(obj -> (LwtVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("瞭望塔", projectRespVO);
                });
        importedData.get("直升机停机坪").getLeft().stream()
                .map(obj -> (ZsjtjpVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("直升机停机坪", projectRespVO);
                });
        importedData.get("军警民联防平台").getLeft().stream()
                .map(obj -> (JjmlfptVo) obj).forEach(zqdl -> {
                    JSONArray jsonArray = convertToJson(zqdl);
                    ProjectRespVO projectRespVO = new ProjectRespVO();
                    BeanUtils.copyProperties(zqdl, projectRespVO);
                    projectRespVO.setOtherExtends(jsonArray.toJSONString());
                    saveOrUpdateData("军警民联防平台", projectRespVO);
                });

    }

    @Override
    public List<String> importExcelDevice(MultipartFile file) {
        try {
            List<ProjectRespVO> list = ExcelUtils.read(file, ProjectRespVO.class);
            Map<String, ProjectRespVO> deviceNumMap = list.stream().collect(Collectors.toMap(ProjectRespVO::getDeviceNum, Function.identity(), (old, newValue) -> newValue));
            List<String> deviceNums = list.stream().map(ProjectRespVO::getDeviceNum).collect(Collectors.toList());
            List<ProjectDO> projectDOList = getProjectByDeviceNum(deviceNums);
            List<ProjectDO> saveList = new ArrayList<>();
            for (ProjectDO projectDO : projectDOList) {
                ProjectRespVO projectRespVOS = deviceNumMap.get(projectDO.getDeviceNum());
                projectDO.setDeviceStatus(projectRespVOS.getDeviceStatus());
                projectDO.setDeviceStatusRemark(projectRespVOS.getDeviceStatusRemark());
                saveList.add(projectDO);
            }
            if (saveList != null && !saveList.isEmpty()) {
                projectMapper.updateBatch(saveList);
            }
            Set<String> existingDeviceNums = projectDOList.stream()
                    .map(ProjectDO::getDeviceNum)
                    .collect(Collectors.toSet());

            return deviceNums.stream()
                    .filter(deviceNum -> !existingDeviceNums.contains(deviceNum))
                    .collect(Collectors.toList());

        } catch (IOException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

    public List<ProjectDO> getProjectByDeviceNum(List<String> deviceNums) {
        LambdaQueryWrapper<ProjectDO> wrapper = new LambdaQueryWrapper<>();
        wrapper.in(ProjectDO::getDeviceNum, deviceNums);
        return projectMapper.selectList(wrapper);
    }

    public static JSONArray convertToJson(Object obj) {
        try {
            JSONArray result = new JSONArray();

            // 获取对象的类
            Class<?> clazz = obj.getClass();

            // 遍历所有字段
            for (Field field : clazz.getDeclaredFields()) {
                // 设置可访问私有字段
                field.setAccessible(true);

                // 获取字段名（code）
                String code = field.getName();

                // 获取字段值（code_val）
                Object value = field.get(obj);
                String codeVal = value != null ? value.toString() : "";

                // 获取字段上的 ExcelProperty 注解（code_txt）
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                String codeTxt = excelProperty != null ? excelProperty.value()[0] : "";

                // 组装 JSON 对象
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("code", code);
                jsonObject.put("code_val", codeVal);
                jsonObject.put("code_txt", codeTxt);

                // 添加到结果数组
                result.add(jsonObject);
            }
            return result;
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private Map<String, Pair<List<?>, Class<?>>> processSpecialGroups(Map<String, List<ProjectRespVO>> sheetDataMap) {
        Map<String, Pair<List<?>, Class<?>>> map = new LinkedHashMap<>();
        sheetDataMap.forEach((key, value) -> {
            try {
                switch (key) {
                    case "执勤道路":
                        List<ZqdlVo> zqdlVos = processGroup(value, ZqdlVo.class);
                        map.put(key, Pair.of(zqdlVos, ZqdlVo.class));
                        break;
                    case "执勤码头":
                        map.put(key, Pair.of(processGroup(value, ZqmtVo.class), ZqmtVo.class));
                        break;
                    case "监控中心":
                        map.put(key, Pair.of(processGroup(value, JkzxVo.class), JkzxVo.class));
                        break;
                    case "监控站":
                        map.put(key, Pair.of(processGroup(value, JkzVo.class), JkzVo.class));
                        break;
                    case "监控前端":
                        map.put(key, Pair.of(processGroup(value, JkqdVo.class), JkqdVo.class));
                        break;
                    case "瞭望塔":
                        map.put(key, Pair.of(processGroup(value, LwtVo.class), LwtVo.class));
                        break;
                    case "直升机停机坪":
                        map.put(key, Pair.of(processGroup(value, ZsjtjpVo.class), ZsjtjpVo.class));
                        break;
                    case "军警民联防平台":
                        map.put(key, Pair.of(processGroup(value, JjmlfptVo.class), JjmlfptVo.class));
                        break;
                    default:
                        // 其他分组保持原样
                        break;
                }
            } catch (Exception e) {
                log.error("处理分组[{}]时发生异常", key, e);
            }
        });
        return map;
    }

    //===================== 通用处理方法 =====================//
    private <T> List<T> processGroup(List<ProjectRespVO> projects, Class<T> targetClass) {
        List<T> resultList = new ArrayList<>();

        projects.forEach(project -> {
            try {
                // 1. 校验必要字段
                if (StringUtils.isBlank(project.getOtherExtends())) {
                    log.warn("项目{}的扩展字段为空", project.getId());
                    return;
                }

                // 2. 创建目标VO实例
                T voInstance = parseFromJson(project.getOtherExtends(), targetClass);

                // 3. 补充项目分类名称
                project.setProjectCateName(
                        codeService.getCodeName(project.getProjectCate())
                );

                // 4. 属性复制
                BeanUtils.copyProperties(project, voInstance);


                // 5. 添加结果集
                resultList.add(voInstance);
            } catch (Exception e) {
                log.error("处理项目{}时发生异常", project.getId(), e);
            }
        });

        return resultList;
    }

    //===================== JSON转换工具方法 =====================//
    private <T> T parseFromJson(String jsonStr, Class<T> targetClass) throws Exception {
        T instance = targetClass.getDeclaredConstructor().newInstance();
        JSONArray jsonArray = JSON.parseArray(jsonStr);

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObj = jsonArray.getJSONObject(i);
            String codeTxt = jsonObj.getString("code_val");
            String code = jsonObj.getString("code");

            setFieldValue(instance, code, codeTxt);
        }
        return instance;
    }

    //===================== 反射字段设置方法 =====================//
    private void setFieldValue(Object target, String code, String codeTxt) {
        try {
            Field field = target.getClass().getDeclaredField(code);
            field.setAccessible(true);

            Class<?> fieldType = field.getType();

            // 类型安全转换
            // 类型安全转换
            Object convertedValue;
            switch (fieldType.getSimpleName()) {
                case "String":
                    convertedValue = codeTxt;
                    break;
                case "Integer":
                case "int":
                    convertedValue = Integer.parseInt(codeTxt);
                    break;
                case "Long":
                case "long":
                    convertedValue = Long.parseLong(codeTxt);
                    break;
                case "Double":
                case "double":
                    convertedValue = Double.parseDouble(codeTxt);
                    break;
                case "Boolean":
                case "boolean":
                    convertedValue = Boolean.parseBoolean(codeTxt);
                    break;
                default:
                    throw new IllegalArgumentException("不支持的字段类型: " + fieldType);
            }

            field.set(target, convertedValue);
        } catch (NoSuchFieldException e) {
            log.warn("字段[{}]在类[{}]中不存在", code, target.getClass().getSimpleName());
        } catch (Exception e) {
            log.error("设置字段[{}]值时发生异常", code, e);
        }
    }

    @SneakyThrows
    public static void writeMultipleSheets(HttpServletResponse response,
                                           String filename,
                                           Map<String, Pair<List<?>, Class<?>>> sheetDataMap) {
        // 设置响应头
        response.addHeader("Content-Disposition", "attachment;filename=" + HttpUtils.encodeUtf8(filename));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                .autoCloseStream(false)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build()) {

            int sheetIndex = 0;
            for (Map.Entry<String, Pair<List<?>, Class<?>>> entry : sheetDataMap.entrySet()) {
                String sheetName = entry.getKey();
                List<?> data = entry.getValue().getLeft();
                Class<?> clazz = entry.getValue().getRight();

                // 动态创建 WriteSheet（关键修改点）
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex++, sheetName)
                        .head(clazz) // 动态指定表头类型
                        .build();

                // 写入数据（需确保 data 的类型与 clazz 匹配）
                excelWriter.write(data, writeSheet);
            }
        }
    }

    @Override
    public List<Map<String, Object>> getDeviceNum(String xmType, String city) {
        return projectMapper.getDeviceNum(xmType, city);
    }
}